* .do file that generated .dta file for each of the individual Finkel analagous aid variables since those variables do not exist online otherwise and were gathered by Finkel from books.  This file is used for a extension/replication of Finkel et al 2007 for recent years.  Other analysis variables will be downloaded full rank and merged in here and in later .do files.

*set folder replicationgovernance\finkelextension folder as base folder

cd "XXX\ReplicationGovernance\Finkelextension"

import delimited "us_foreign_aid_completecsv.csv", encoding(UTF-8) 

*drop obligations, keep only disbursements---which we have starting in 2001
drop if transaction_type_name=="Obligations"

*destring fiscal_year, which used to have some typos that made it string.
destring fiscal_year, replace

*drop if fiscal_year<2004
drop if fiscal_year<2001

*one way or another, we will not have other data for 2019
drop if fiscal_year>2018

*start with non-USAID aid
*create nonUSAID variable
*create indicator for all non-USAIDcategories

gen nonUSAID=1

*categories that are USAID.  Only USAID in this data.

replace nonUSAID=0 if implementing_agency_id==1

*Sum data for country by year that are democracy and governance (AID_100)---governance codes in variable USG_sector_name are: 

*Democracy, Human Rights and Governance

gen dgaid = 0
replace dgaid=1 if usg_sector_id==46

*Civil Society

replace dgaid=1 if usg_sector_id==10

*Good governance

replace dgaid=1 if usg_sector_id==8

*Political competition and consensus

replace dgaid=1 if usg_sector_id==9

*Rule of law and human rights

replace dgaid=1 if usg_sector_id==7

*sum up these categories, USAID dg aid (~ AID_100)

sort fiscal_year country_id
by fiscal_year country_id: egen USAIDdgconstant2016=sum(constant_amount) if nonUSAID==0 & dgaid==1 

*Fill non-zero values with the maximum value to prepare for collapse to country-year unit
replace USAIDdgconstant2016=0 if USAIDdgconstant2016==.
by fiscal_year country_id: egen USAIDdgconstant2016repeat = max(USAIDdgconstant2016)

*Sum USAID data for country by year that are not governance (~ AID_000)

sort fiscal_year country_id
by fiscal_year country_id: egen USAIDnondgconstant2016=sum(constant_amount) if nonUSAID==0 & dgaid==0

*Fill non-zero values with the maximum value to prepare for collapse to country-year unit
replace USAIDnondgconstant2016=0 if USAIDnondgconstant2016==.
by fiscal_year country_id: egen USAIDnondgconstant2016repeat = max(USAIDnondgconstant2016)

*Sum data across all US agencies and projects that are not USAID per year.  (~ Variable AID_2+state dept aid)
sort fiscal_year country_id
by fiscal_year country_id: egen nonUSAIDconstant2016=sum(constant_amount) if nonUSAID==1

*Fill non-zero values with the maximum value to prepare for collapse to country-year unit
replace nonUSAIDconstant2016=0 if nonUSAIDconstant2016==.
by fiscal_year country_id: egen nonUSAIDconstant2016repeat = max(nonUSAIDconstant2016)

*Finkel eliminates high income countries.  Mostly gets rid of some aid to post-Balkan war countries and some gulf countries.  Not a large number.  That will be handled in the analysis stage using income level restrictions on analysis instead.

*****clean up

*generate tag for single observation to keep for each country_id

egen singlecountryyear=tag(fiscal_year country_id)

*drop regional aid information, will be used in a different more complicated process to generate variables.

drop if country_id>999

*drop all excess data to get down to country-year level of aggregation

keep if singlecountryyear==1

*keep only variables which are at country-year level.

keep country_id country_code country_name region_id region_name income_group_id income_group_name USAIDdgconstant2016repeat USAIDnondgconstant2016repeat nonUSAIDconstant2016repeat fiscal_year

*rename variables to merge in v-dem full rank data

rename fiscal_year year
rename country_code country_text_id

*drop cases made to unclear distributions between former countries/regions that are separate from their component countries, which are also in the data.

drop if country_text_id=="SCG"
drop if country_text_id=="SDF"

*Save so that it can be merged into vdem v9.

save "USAIDdata20022018.dta", replace







